package snapex.expense.service.impl;

import static org.mybatis.dynamic.sql.SqlBuilder.isEqualTo;
import static org.mybatis.dynamic.sql.SqlBuilder.isGreaterThanWhenPresent;
import static org.mybatis.dynamic.sql.SqlBuilder.isIn;
import static org.mybatis.dynamic.sql.SqlBuilder.isNotNull;
import static org.mybatis.dynamic.sql.SqlBuilder.isNull;

import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashSet;
import java.util.List;
import java.util.Set;
import java.util.function.Predicate;

import org.mybatis.dynamic.sql.select.MyBatis3SelectModelAdapter;
import org.mybatis.dynamic.sql.select.QueryExpressionDSL;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.ParameterizedPreparedStatementSetter;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;

import lombok.extern.slf4j.Slf4j;
import snapex.core.mapper.ExpenseCashAdvanceDynamicSqlSupport;
import snapex.core.mapper.ExpenseCashAdvanceMapper;
import snapex.core.mapper.ExpenseDetailDynamicSqlSupport;
import snapex.core.mapper.ExpenseDetailMapper;
import snapex.core.mapper.ExpenseDynamicSqlSupport;
import snapex.core.mapper.ext.ExpenseMapperExt;
import snapex.core.mapper.ext.ExpenseSqlProvider;
import snapex.core.model.Expense;
import snapex.core.model.ExpenseCashAdvance;
import snapex.core.model.ExpenseDetail;
import snapex.core.model.Result;
import snapex.core.model.SearchParams;
import snapex.expense.service.IExpenseService;

@Slf4j
@Service
@Transactional
public class ExpenseService implements IExpenseService {

	@Autowired
	private ExpenseMapperExt expenseMapper;
	
	@Autowired
	private ExpenseDetailMapper expenseDetailMapper;

	@Autowired
	private ExpenseCashAdvanceMapper expenseCashAdvanceMapper;
	
//	@Autowired
//	private EmployeeHashMapperExt employeeHashMapper;
//		
//	@Autowired
//	private StaffInfoMapperExt staffInfoMapper;
	
	@Autowired @Qualifier("snapexJdbcTemplate")
	private JdbcTemplate snapexJdbcTemplate;
	
	@Value("${snapex.jasonBatchSize}")
	private int jasonBatchSize;
	
	@Override
	public Result importExpenses(List<Expense> expenses) {
		
		log.debug("*******Start to Import expenses:{}", expenses.size());
		if(expenses.isEmpty()) {
			return Result.success().msg("No record to import");
		}
		
		Set<String> expenseIds = new HashSet<String>();		
		
		expenses.forEach( e -> expenseIds.add(e.getExpenseId()));
		
		String[] ids = expenseIds.toArray(new String[] {});
		
		//remove records which have been imported before to avoid duplicated records 
		Set<String> existingExpenseIds = new HashSet<String>();		
		expenseMapper.selectByExample().where(ExpenseDynamicSqlSupport.expenseId, isIn(ids)).build().execute().forEach(e -> existingExpenseIds.add(e.getExpenseId()));
				
		expenses.removeIf(new Predicate<Expense>() {
			@Override
			public boolean test(Expense t) {				
				return existingExpenseIds.contains(t.getExpenseId());			
			}			
		});
		
		if(expenses.isEmpty()) {
			return Result.success().msg("Records already exist, no need to update again. ExpenseIds:" + existingExpenseIds.toString());
		}
		
		List<ExpenseDetail> purposes = new ArrayList<ExpenseDetail>();
		List<ExpenseCashAdvance> cashes = new ArrayList<ExpenseCashAdvance>();		
		expenseIds.clear();		
		expenses.forEach( e -> {
			expenseIds.add(e.getExpenseId());
			int index = 0;
			List<ExpenseDetail> details = e.getPurposes();
			for(ExpenseDetail detail : details) {
				detail.setId(detail.getExpenseDetailId());
				detail.setExpenseDetailId(""+index);
				detail.setListIndex(index++);				
			}
			
			purposes.addAll(e.getPurposes());			
			cashes.addAll(e.getDeductions());			
		});
		
		//expenseMapper.deleteByExample().where(ExpenseDynamicSqlSupport.expenseId, isIn(ids)).build().execute();
		//expenseDetailMapper.deleteByExample().where(ExpenseDetailDynamicSqlSupport.expenseId, isIn(ids)).or(ExpenseDetailDynamicSqlSupport.id, isIn(ids)).build().execute();
					
		snapexJdbcTemplate.batchUpdate(ExpenseSqlProvider.insertExpense(), expenses, jasonBatchSize, new  ParameterizedPreparedStatementSetter<Expense>() {
			@Override
			public void setValues(PreparedStatement ps, Expense expense) throws SQLException {
				ps.setString(1, expense.getExpenseId());
				ps.setInt(2, expense.getStatus());
				ps.setString(3, expense.getNextApprover());
				ps.setString(4, expense.getLastApprover());
				ps.setString(5, expense.getStaffId());
				ps.setString(6, expense.getExtensionNo());
				ps.setInt(7, expense.getSiteId());				
				ps.setBigDecimal(8, expense.getTotalAmount());
				ps.setBigDecimal(9, expense.getExpenseCashAdvanceAmount());
				ps.setDate(10, expense.getSubmittedDate());
				ps.setDate(11, expense.getConfirmedDate());
				ps.setString(12, expense.getClarityProjectId()==null?"":expense.getClarityProjectId());
				ps.setString(13, expense.getClarityProjectName()==null?"":expense.getClarityProjectName());
				ps.setString(14, expense.getCustomerCostCentreName()==null?"":expense.getCustomerCostCentreName());
				ps.setString(15, expense.getCustomerCostCentreNo()==null?"":expense.getCustomerCostCentreNo());				
				ps.setInt(16, expense.getBillable());
				ps.setString(17, expense.getCustomerCostCentreMandatory()==null?"":expense.getCustomerCostCentreMandatory());
				ps.setString(18, expense.getUsername());
				ps.setString(19, expense.getDescription());
				ps.setTimestamp(20, expense.getCreateTimestamp());
				ps.setTimestamp(21, expense.getSyncTimestamp());				
			}			
		});
			
		snapexJdbcTemplate.batchUpdate(ExpenseSqlProvider.insertExpenseDetail(), purposes, jasonBatchSize, new  ParameterizedPreparedStatementSetter<ExpenseDetail>() {
			@Override
			public void setValues(PreparedStatement ps, ExpenseDetail ed) throws SQLException {
				ps.setString(1, ed.getExpenseDetailId());
				ps.setString(2, ed.getExpenseId());
				ps.setString(3, ed.getOccurDate());
				ps.setString(4, ed.getDescription());
				ps.setString(5, ed.getCurrency());
				ps.setBigDecimal(6, ed.getAmount());				
				ps.setInt(7, ed.getPurposeId());				
				ps.setString(8, ed.getId());				
				ps.setInt(9, ed.getListIndex());
				ps.setString(10, ed.getInvoiceId());
				ps.setInt(11, ed.getStatus());				
				ps.setTimestamp(12, ed.getCreateTimestamp());
				ps.setTimestamp(13, ed.getSyncTimestamp());
			}			
		});
				
		snapexJdbcTemplate.execute(ExpenseSqlProvider.updateStaffId());
		snapexJdbcTemplate.execute(ExpenseSqlProvider.updateExtensionNumber());
		snapexJdbcTemplate.execute(ExpenseSqlProvider.updateNextApprover());
		snapexJdbcTemplate.execute(ExpenseSqlProvider.updateCostCenterMandatory());
		snapexJdbcTemplate.execute(ExpenseSqlProvider.updateCostCenterName());
		snapexJdbcTemplate.execute(ExpenseSqlProvider.updateCostCenter());

		StringBuilder msg = new StringBuilder();
		if(!existingExpenseIds.isEmpty()) {
			msg.append("Exepenses already exist, ignored. ExpenseIds:").append(existingExpenseIds.toString()).append(";");			
		}
		msg.append("Number of exepenses updated:" ).append(expenseIds.size() ).append(", Expense ids:").append(expenseIds.toString());
		return Result.success().msg(msg.toString());
	}
	

	
	@Override
	public List<Expense> exportExpenses(SearchParams condition){
		
		List<Expense> expenses = this.getExpenses(condition);
		for(Expense expense : expenses) {			
			expense.setPurposes(expenseDetailMapper.selectByExample()
					.where(ExpenseDetailDynamicSqlSupport.expenseId, isEqualTo(expense.getExpenseId())).build().execute());

			expense.setDeductions(expenseCashAdvanceMapper.selectByExample()
					.where(ExpenseCashAdvanceDynamicSqlSupport.expenseId, isEqualTo(expense.getExpenseId())).build().execute());
		}
		
		return expenses;
	}
	
	@Override
	public List<Expense> getExpenses(SearchParams params) {
		
		return  expensesQueryBuilder(params).build().execute();
	}
		
	private QueryExpressionDSL<MyBatis3SelectModelAdapter<List<Expense>>>.QueryExpressionWhereBuilder expensesQueryBuilder(SearchParams params) {

		QueryExpressionDSL<MyBatis3SelectModelAdapter<List<Expense>>>.QueryExpressionWhereBuilder queryBuilder = expenseMapper
				.selectByExample().where(ExpenseDynamicSqlSupport.expenseId, isNotNull());

		if (params.getExpenseId() != null) {
			queryBuilder.and(ExpenseDynamicSqlSupport.expenseId, isEqualTo(params.getExpenseId()));
		}

		if (params.getStatus() != null) {
			queryBuilder.and(ExpenseDynamicSqlSupport.status, isEqualTo(params.getStatus()));
		}

		if(params.getSubmittedDate() != null) {
			queryBuilder.and(ExpenseDynamicSqlSupport.submittedDate, isEqualTo(params.getSubmittedDate()));
		}
		
		if(params.getProjectId() != null) {
			queryBuilder.and(ExpenseDynamicSqlSupport.clarityProjectId, isEqualTo(params.getProjectId()));
		}
		
		if(params.getStaffId() != null) {
			queryBuilder.and(ExpenseDynamicSqlSupport.staffId, isEqualTo(params.getStaffId()));
		}
		
		if(params.getSyncTimestamp() != null) {
			queryBuilder.and(ExpenseDynamicSqlSupport.syncTimestamp, isGreaterThanWhenPresent(params.getSyncTimestamp()));
		}
		
		if(params.isOnlyNotSync()) {
			queryBuilder.and(ExpenseDynamicSqlSupport.syncTimestamp, isNull());
		}
		
		return queryBuilder;
	}
} 
